package com.es.hr.areartm;

import java.sql.ResultSet;
import java.util.ArrayList;


import com.es.business.area.AreaBean;
import com.es.business.area.AreaBiz;
import com.es.hr.emp.EmpInfoBean;
import com.es.util.db.Dao;
import com.es.util.page.PageBean;
import com.es.util.page.PageNavigator;

public class AreaRTMBiz {
	public PageBean searchAreaByRTM(String empId, int curpage)
	throws Exception {
		Dao dao = new Dao();
		PageBean pageBean = new PageBean();
		ArrayList<String> query = new ArrayList<String>();
		try {
			String sql = "select area_rtm_id, area_code, area_value, emp_code, emp_name from hrm_area_rtm where emp_id=? ";
			query.add(empId);
	int total = dao.getAllRow(sql, query);
	PageNavigator pageNav = new PageNavigator();
	pageNav.setCurpage(curpage);
	pageNav .setRowTotal(total);
	pageNav.setPageTotal(total % pageNav.getPageRow() == 0 ? total
			/ pageNav.getPageRow() : total / pageNav.getPageRow() + 1);
	pageBean = dao.queryPageList(sql, pageNav, query);
		} finally {
			dao.close();
		}
		return pageBean;
	}	
	
	public boolean deleteAreaRTMById(String id) throws Exception{
		Dao dao = new Dao();
		boolean result = false;
		String sql = "delete hrm_area_rtm where area_rtm_id = ?";
		ArrayList<String> sqlParam = new ArrayList<String>();
		sqlParam.add(id);
		try{
			dao.update(sql, sqlParam);
			result = true;
		}catch(Exception ex){
			throw ex;
		}finally{
			dao.close();
		}
		return result;
	}
	
	public ArrayList<AreaBean> searchNotAreaByRTM(String empId)
	throws Exception {
		Dao dao = new Dao();
		ArrayList<AreaBean> areaList = new AreaBiz().getAreaList();
		ArrayList<String> query = new ArrayList<String>();
		query.add(empId);
		try {
			String sql = "select area_code from hrm_area_rtm where emp_id=? ";
			ResultSet rs = dao.Select(sql, query);
			while(rs.next()){
				String areaCode = rs.getString(1);
				for(int i = 0; i < areaList.size(); ++i){
					AreaBean area = areaList.get(i);
					if(area.getAreaCode().equals(areaCode)){
						areaList.remove(area);
						--i;
					}
				}
			}
		} finally {
			dao.close();
		}
		return areaList;
	}	
	
	public EmpInfoBean getEmpById(String empId)
	throws Exception {
		Dao dao = new Dao();
		EmpInfoBean empBean = new EmpInfoBean() ;
		ArrayList<String> query = new ArrayList<String>();
		query.add(empId);
		try {
			String sql = "select emp_code,emp_name from hrm_emp_info where emp_id=? ";
			ResultSet rs = dao.Select(sql, query);
			while(rs.next()){
				empBean.emp_code = rs.getString(1);
				empBean.emp_name = rs.getString(2);
			}
		}catch(Exception ex){
			throw ex;
		}finally {
			dao.close();
		}
		return empBean;
	}	
	
	public boolean insertAreaRTM(AreaBean area, EmpInfoBean empBean, String empId)
	throws Exception {
		Dao dao = new Dao();
		boolean result = false;
		ArrayList<String> query = new ArrayList<String>();
		query.add(area.getAreaCode());
		query.add(area.getAreaValue());
		query.add(empBean.emp_code);
		query.add(empBean.emp_name);
		query.add(empId);
		try {
			String sql = "insert into hrm_area_rtm (area_code, area_value, emp_code, emp_name, emp_id) values(?,?,?,?,?) ";
			dao.update(sql, query);
			result = true;
		}catch(Exception ex){
			throw ex;
		} finally {
			dao.close();
		}
		return result;
	}	
	
	public PageBean queryEmpList(PageNavigator pageNav, ArrayList<String> param)
	throws Exception {
		Dao dao = new Dao();
		PageBean pageBean = new PageBean();
		ArrayList<String> query = new ArrayList<String>();
		try {
			String sql = "select emp.emp_id,emp.emp_area_value,ltrim(rtrim(emp.emp_login_yn)) emp_login_yn,emp.emp_province_value,emp.emp_city_value,emp.emp_name,emp.emp_login_id,(select top 1 common_value from hrm_common "
				+ "where common_code = emp.emp_role and common_type='role') emp_role," 
				+ "(select top 1 a.emp_name from hrm_emp_info a where a.emp_ctm_code = emp.emp_id) ctm_name,"
				+ "emp.emp_code,emp.emp_job,emp.emp_phone_mobile,emp.emp_phone_native,emp.emp_mail,emp.shop_name" 
				+ " from hrm_emp_info emp where emp.del_flag='N' ";
			String[] sqlArrary = new String[] { "and emp.emp_area_code=? ",
					"and emp.emp_province_code=? ", "and emp.emp_city_code=? ",
					"and UPPER(emp.emp_login_id) like '%'+UPPER(?)+'%' ",
					"and emp.emp_role=? ",
					"and UPPER(emp.emp_name) like '%'+UPPER(?)+'%'",
					"and UPPER(emp.emp_code) like '%'+UPPER(?)+'%'",
				"and UPPER(emp.emp_job) like '%'+UPPER(?)+'%'"};
			for (int i = 0; i < param.size(); ++i) {
				if (!param.get(i).equals("")) {
					sql += sqlArrary[i];
					query.add(param.get(i).trim());
				}
			}
			int total = dao.getAllRow(sql, query);
			pageNav.setRowTotal(total);
			pageNav.setPageTotal(total % pageNav.getPageRow() == 0 ? total
					/ pageNav.getPageRow() : total / pageNav.getPageRow() + 1);
			pageBean = dao.queryPageList(sql, pageNav, query);
		} finally {
			dao.close();
		}
		return pageBean;
	}
	
	public String computeCountByEmpId(String empId)throws Exception {
		Dao dao = new Dao();
		String result = "0";
		ArrayList<String> query = new ArrayList<String>();
		query.add(empId.toString());
		try {
			String sql = "select count(*) from hrm_area_rtm where emp_id=? ";
			ResultSet rs = dao.Select(sql, query);
			while(rs.next()){
				result = rs.getString(1);
			}
		}catch(Exception ex){
			throw ex;
		}finally {
			dao.close();
		}
		return result;
	}	
}
